
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/22/2010 16:02:12
-- Generated from EDMX file: D:\Programaçao\Temp\SurveyCommit\Survey.Data\SurveyORM.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SurveyApp];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_DirectorSchool]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SchoolSet] DROP CONSTRAINT [FK_DirectorSchool];
GO
IF OBJECT_ID(N'[dbo].[FK_TeacherSchool]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeacherSet] DROP CONSTRAINT [FK_TeacherSchool];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentClassSchool]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentClassSet] DROP CONSTRAINT [FK_StudentClassSchool];
GO
IF OBJECT_ID(N'[dbo].[FK_TeacherStudentClass_Teacher]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeacherStudentClass] DROP CONSTRAINT [FK_TeacherStudentClass_Teacher];
GO
IF OBJECT_ID(N'[dbo].[FK_TeacherStudentClass_StudentClass]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TeacherStudentClass] DROP CONSTRAINT [FK_TeacherStudentClass_StudentClass];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentClassStudent]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentSet] DROP CONSTRAINT [FK_StudentClassStudent];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentSchool]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StudentSet] DROP CONSTRAINT [FK_StudentSchool];
GO
IF OBJECT_ID(N'[dbo].[FK_SurveyGlobalSurvey]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SurveyEntitySet] DROP CONSTRAINT [FK_SurveyGlobalSurvey];
GO
IF OBJECT_ID(N'[dbo].[FK_SurveyQuestionary]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[QuestionarySet] DROP CONSTRAINT [FK_SurveyQuestionary];
GO
IF OBJECT_ID(N'[dbo].[FK_QuestionaryQuestion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[QuestionSet] DROP CONSTRAINT [FK_QuestionaryQuestion];
GO
IF OBJECT_ID(N'[dbo].[FK_StudentQuestionary]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[QuestionarySet] DROP CONSTRAINT [FK_StudentQuestionary];
GO
IF OBJECT_ID(N'[dbo].[FK_LearningMaterialSurvey]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SurveyEntitySet] DROP CONSTRAINT [FK_LearningMaterialSurvey];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[DirectorSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[DirectorSet];
GO
IF OBJECT_ID(N'[dbo].[LearningMaterialSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[LearningMaterialSet];
GO
IF OBJECT_ID(N'[dbo].[SchoolSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SchoolSet];
GO
IF OBJECT_ID(N'[dbo].[StudentSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentSet];
GO
IF OBJECT_ID(N'[dbo].[StudentClassSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StudentClassSet];
GO
IF OBJECT_ID(N'[dbo].[TeacherSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeacherSet];
GO
IF OBJECT_ID(N'[dbo].[GlobalSurveySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[GlobalSurveySet];
GO
IF OBJECT_ID(N'[dbo].[SurveyEntitySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SurveyEntitySet];
GO
IF OBJECT_ID(N'[dbo].[QuestionSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[QuestionSet];
GO
IF OBJECT_ID(N'[dbo].[QuestionarySet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[QuestionarySet];
GO
IF OBJECT_ID(N'[dbo].[TeacherStudentClass]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TeacherStudentClass];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'DirectorSet'
CREATE TABLE [dbo].[DirectorSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'LearningMaterialSet'
CREATE TABLE [dbo].[LearningMaterialSet] (
    [Id] uniqueidentifier  NOT NULL,
    [MaterialName] nvarchar(max)  NULL,
    [MaterialCode] int  NOT NULL,
    [NumberOfPages] int  NOT NULL,
    [Description] nvarchar(max)  NULL
);
GO

-- Creating table 'SchoolSet'
CREATE TABLE [dbo].[SchoolSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Adress] nvarchar(max)  NOT NULL,
    [ZipCode] nvarchar(max)  NOT NULL,
    [Neighborhood] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [State] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Fax] nvarchar(max)  NOT NULL,
    [ExpedientBegin] nvarchar(max)  NOT NULL,
    [ExpedientEnd] nvarchar(max)  NOT NULL,
    [Director_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'StudentSet'
CREATE TABLE [dbo].[StudentSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Age] int  NOT NULL,
    [StudentClass_Id] uniqueidentifier  NULL,
    [School_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'StudentClassSet'
CREATE TABLE [dbo].[StudentClassSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Grade] int  NOT NULL,
    [School_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'TeacherSet'
CREATE TABLE [dbo].[TeacherSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [MatterTaught] int  NOT NULL,
    [WorkPeriod] int  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [School_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'GlobalSurveySet'
CREATE TABLE [dbo].[GlobalSurveySet] (
    [Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'SurveyEntitySet'
CREATE TABLE [dbo].[SurveyEntitySet] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [CurrentStep] int  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- Creating table 'QuestionSet'
CREATE TABLE [dbo].[QuestionSet] (
    [Id] uniqueidentifier  NOT NULL,
    [Statement] nvarchar(max)  NOT NULL,
    [TypeOfAnswer] int  NOT NULL,
    [Questionary_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'QuestionarySet'
CREATE TABLE [dbo].[QuestionarySet] (
    [Id] uniqueidentifier  NOT NULL,
    [Code] int  NOT NULL,
    [IsAnswered] bit  NOT NULL,
    [IsControl] bit  NOT NULL,
    [Step] int  NOT NULL,
    [FillDate] datetime  NOT NULL,
    [Student_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'TeacherStudentClass'
CREATE TABLE [dbo].[TeacherStudentClass] (
    [Teacher_Id] uniqueidentifier  NOT NULL,
    [StudentClass_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'DirectorSet'
ALTER TABLE [dbo].[DirectorSet]
ADD CONSTRAINT [PK_DirectorSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'LearningMaterialSet'
ALTER TABLE [dbo].[LearningMaterialSet]
ADD CONSTRAINT [PK_LearningMaterialSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SchoolSet'
ALTER TABLE [dbo].[SchoolSet]
ADD CONSTRAINT [PK_SchoolSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StudentSet'
ALTER TABLE [dbo].[StudentSet]
ADD CONSTRAINT [PK_StudentSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StudentClassSet'
ALTER TABLE [dbo].[StudentClassSet]
ADD CONSTRAINT [PK_StudentClassSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'TeacherSet'
ALTER TABLE [dbo].[TeacherSet]
ADD CONSTRAINT [PK_TeacherSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'GlobalSurveySet'
ALTER TABLE [dbo].[GlobalSurveySet]
ADD CONSTRAINT [PK_GlobalSurveySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SurveyEntitySet'
ALTER TABLE [dbo].[SurveyEntitySet]
ADD CONSTRAINT [PK_SurveyEntitySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'QuestionSet'
ALTER TABLE [dbo].[QuestionSet]
ADD CONSTRAINT [PK_QuestionSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'QuestionarySet'
ALTER TABLE [dbo].[QuestionarySet]
ADD CONSTRAINT [PK_QuestionarySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Teacher_Id], [StudentClass_Id] in table 'TeacherStudentClass'
ALTER TABLE [dbo].[TeacherStudentClass]
ADD CONSTRAINT [PK_TeacherStudentClass]
    PRIMARY KEY NONCLUSTERED ([Teacher_Id], [StudentClass_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Director_Id] in table 'SchoolSet'
ALTER TABLE [dbo].[SchoolSet]
ADD CONSTRAINT [FK_DirectorSchool]
    FOREIGN KEY ([Director_Id])
    REFERENCES [dbo].[DirectorSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DirectorSchool'
CREATE INDEX [IX_FK_DirectorSchool]
ON [dbo].[SchoolSet]
    ([Director_Id]);
GO

-- Creating foreign key on [School_Id] in table 'TeacherSet'
ALTER TABLE [dbo].[TeacherSet]
ADD CONSTRAINT [FK_TeacherSchool]
    FOREIGN KEY ([School_Id])
    REFERENCES [dbo].[SchoolSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeacherSchool'
CREATE INDEX [IX_FK_TeacherSchool]
ON [dbo].[TeacherSet]
    ([School_Id]);
GO

-- Creating foreign key on [School_Id] in table 'StudentClassSet'
ALTER TABLE [dbo].[StudentClassSet]
ADD CONSTRAINT [FK_StudentClassSchool]
    FOREIGN KEY ([School_Id])
    REFERENCES [dbo].[SchoolSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentClassSchool'
CREATE INDEX [IX_FK_StudentClassSchool]
ON [dbo].[StudentClassSet]
    ([School_Id]);
GO

-- Creating foreign key on [Teacher_Id] in table 'TeacherStudentClass'
ALTER TABLE [dbo].[TeacherStudentClass]
ADD CONSTRAINT [FK_TeacherStudentClass_Teacher]
    FOREIGN KEY ([Teacher_Id])
    REFERENCES [dbo].[TeacherSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [StudentClass_Id] in table 'TeacherStudentClass'
ALTER TABLE [dbo].[TeacherStudentClass]
ADD CONSTRAINT [FK_TeacherStudentClass_StudentClass]
    FOREIGN KEY ([StudentClass_Id])
    REFERENCES [dbo].[StudentClassSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeacherStudentClass_StudentClass'
CREATE INDEX [IX_FK_TeacherStudentClass_StudentClass]
ON [dbo].[TeacherStudentClass]
    ([StudentClass_Id]);
GO

-- Creating foreign key on [StudentClass_Id] in table 'StudentSet'
ALTER TABLE [dbo].[StudentSet]
ADD CONSTRAINT [FK_StudentClassStudent]
    FOREIGN KEY ([StudentClass_Id])
    REFERENCES [dbo].[StudentClassSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentClassStudent'
CREATE INDEX [IX_FK_StudentClassStudent]
ON [dbo].[StudentSet]
    ([StudentClass_Id]);
GO

-- Creating foreign key on [School_Id] in table 'StudentSet'
ALTER TABLE [dbo].[StudentSet]
ADD CONSTRAINT [FK_StudentSchool]
    FOREIGN KEY ([School_Id])
    REFERENCES [dbo].[SchoolSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentSchool'
CREATE INDEX [IX_FK_StudentSchool]
ON [dbo].[StudentSet]
    ([School_Id]);
GO

-- Creating foreign key on [Questionary_Id] in table 'QuestionSet'
ALTER TABLE [dbo].[QuestionSet]
ADD CONSTRAINT [FK_QuestionaryQuestion]
    FOREIGN KEY ([Questionary_Id])
    REFERENCES [dbo].[QuestionarySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionaryQuestion'
CREATE INDEX [IX_FK_QuestionaryQuestion]
ON [dbo].[QuestionSet]
    ([Questionary_Id]);
GO

-- Creating foreign key on [Student_Id] in table 'QuestionarySet'
ALTER TABLE [dbo].[QuestionarySet]
ADD CONSTRAINT [FK_StudentQuestionary]
    FOREIGN KEY ([Student_Id])
    REFERENCES [dbo].[StudentSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StudentQuestionary'
CREATE INDEX [IX_FK_StudentQuestionary]
ON [dbo].[QuestionarySet]
    ([Student_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------